---
title: 'Ingesting from Postgres'
description: With the `postgres` source, Sequin turns the Postgres WAL into a message stream. This means you can stream creates, updates, and deletes from any table in Postgres to your app. Then, you can use Sequin's pull-based or push-based (i.e. webhook) consumption patterns.
---

<Note>This guide assumes you have already created a stream and a consumer. If not, please see the [quickstart guide](/quickstart).</Note>

Sequin is a great fit if LISTEN/NOTIFY's at-most-once delivery is not sufficient for your use case. It's an alternative to setting up Debezium/Kafka to consume Postgres changes.

## Setup

From the CLI, connect any Postgres table to Sequin with:

```bash
sequin source postgres add
```

If connecting your first Postgres database to Sequin, select "Connect new" from the prompt:

```bash
? Choose a database or connect a new one:  [Use arrows to move, type to filter]
> Connect new database
```

And fill out the form:

```bash
Database name:
> postgres

Hostname:
> localhost

Port:
> 5432

Username:
> postgres

Password:
> ********

Give your database a name:
> mydb

Use SSL?

> [X]

[ Submit ]
```

You'll then be prompted with:

```bash
? Do you have an existing Postgres replication slot and publication for Sequin to connect to?
(If you didn't just setup a replication slot for Sequin before this, you probably don't) (y/N)
```

You can indicate `N` (see instructions below for how to setup a replication slot and publication manually).

The walkthrough will then ask:

```bash
We suggest the following defaults:

Slot name: sequin_slot
Publication name: sequin_pub

? Do these defaults look OK? (Y/n)
```

You can accept the defaults by pressing `Enter`. You can accept this one too:

```bash
? Do you want me to setup the replication slot and publication for you? (Y/n)
```

Then, the CLI will ask you to select the tables you want to stream to Sequin:

```bash
? Choose the schema your tables are in (you can edit this list later):  [Use arrows to move, type to filter]
> public
```

Then, you'll be prompted about backfills:

```bash
? Do you want to backfill existing rows from your table(s) into the stream?
```

"Backfilling existing rows" means Sequin will copy all existing rows from your tables into the stream. Then, it will begin streaming changes. If you select "No", Sequin will only stream changes from this point forward.

## Configuration

### Setting up a replication slot and publication

To capture changes from your database, Sequin needs (1) a replication slot and (2) a publication. You have two options to set this up:

#### Automated setup

The CLI will ask permission to create a replication slot and publication for you. Setting up automatically will configure the replication slot to capture _all_ changes to all the tables you selected.

#### Manual setup

If you prefer, you can create the replication slot and publication manually. Setting up manually gives you more control over the publication. You can select whether to include `INSERT`, `UPDATE`, `DELETE`, and `TRUNCATE` events. You can also specify `WHERE` clauses to filter which rows are specified to Sequin.

<Note>The manual setup may be necessary if the Postgres user you've provided during the setup process doesn't have permission to create a replication slot or publication.</Note>

To create a replication slot and publication manually, run the following SQL commands:

```sql
SELECT * FROM pg_create_logical_replication_slot('sequin', 'pgoutput');
-- Create a publication for a select list of tables
CREATE PUBLICATION sequin FOR TABLE my_table, my_other_table WITH (publish_via_partition_root = true);
-- Or, create a publication for all tables in a schema
CREATE PUBLICATION sequin FOR TABLES IN SCHEMA my_schema WITH (publish_via_partition_root = true);
-- Or, create a publication for all tables in the database
CREATE PUBLICATION sequin FOR ALL TABLES WITH (publish_via_partition_root = true);
```

Learn more about publication and the available options [in the Postgres docs](https://www.postgresql.org/docs/current/logical-replication-publication.html).

### Select a format for keys

Sequin will capture changes to your tables and insert them into your stream as messages. The CLI will prompt you for which format you want to use for the keys:

1. `[<database>].[<schema>].[<table>].[<row-id>]`
2. `[<database>].[<schema>].[<table>].[<operation>].[<row-id>]`

Where:

- `<database>` is the name in Sequin you set for the database
- `<schema>` is the name of the schema
- `<table>` is the name of the table
- `<row-id>` is the primary key of the row
- `<operation>` is the operation type (`insert`, `update`, `delete`, `truncate`)

For example:

- `mydb.public.users.1` (format 1)
- `mydb.public.users.insert.1` (format 2)

<Note>More complex key and data transformations are coming soon. Please open an issue specifying your use case if this interests you.</Note>

<Warning>Truncates are not supported yet. If you need to capture truncates, please open an issue specifying your use case.</Warning>

## Shape of messages

All messages are stored in Sequin as a string-encoded JSON. The following fields are always present:

- `data`: The current state of the record.
- `deleted`: A boolean indicating whether the record is deleted.
- `changes`: A map of changed fields (for updates only). Values are the old (previous) values.
- `action`: The type of operation ("insert", "update", or "delete").

### Insert

For insert operations, the message will look like this:

```json
{
  "data": {
    "id": 1,
    "name": "Paul Atreides",
    "house": "Atreides",
    "planet": "Caladan"
  },
  "deleted": false,
  "changes": {},
  "action": "insert"
}
```

### Update

For update operations, the message will include the new state of the record and `changes`:

```json
{
  "data": {
    "id": 1,
    "name": "Paul Atreides",
    "house": "Atreides",
    "planet": "Arrakis"
  },
  "deleted": false,
  "changes": {
    "planet": "Caladan"
  },
  "action": "update"
}
```

Note that the `changes` field contains the old values of the updated fields. In the above example, the prior value of `planet` was `Caladan`.

<Note>
`changes` will only be populated if the replica identity is set to `full`. To change the replica identity, run the following SQL command:

```sql
ALTER TABLE my_table REPLICA IDENTITY FULL;
```
</Note>

### Delete

For delete operations, the message will contain the state of the record before deletion:

```json
{
  "data": {
    "id": 1,
    "name": "Paul Atreides",
    "house": "Atreides",
    "planet": "Arrakis"
  },
  "deleted": true,
  "changes": {},
  "action": "delete"
}
```

The `deleted` field is set to `true` for delete operations.

### Key Format

The key for each message in the stream will follow one of two formats, depending on your configuration:

1. Basic format: `[<database>].[<schema>].[<table>].[<row-id>]`
2. With operation format: `[<database>].[<schema>].[<table>].[<operation>].[<row-id>]`

For example, with the operation format:

- Insert: `mydb.public.users.insert.1`
- Update: `mydb.public.users.update.1`
- Delete: `mydb.public.users.delete.1`

### Backfilling

The CLI will next ask if you want to backfill existing rows from your tables into the stream. This
will run a process to extract all existing rows from your tables and insert them into your stream.
(Note that all rows will be inserted as `insert` operations.)

## Status

You can monitor the status of the `postgres` source at any time by running:

```bash
sequin source postgres info [<source>]
```

The `Last Committed Timestamp` indicates the last WAL position that Sequin has committed to the stream.

## Adding and removing tables

You can add or remove tables that are shipped to Sequin by modifing the publication in Postgres:

```sql
-- Add a table to the publication
alter publication sequin_publication add table my_schema.new_table;

-- Remove a table from the publication
alter publication sequin_publication drop table my_schema.old_table;
```